﻿using System;
using System.Collections.Generic;
using System.Text;

namespace DBScript
{
    /// <summary>
    /// 数据库查询(MsSQL)
    /// </summary>
    public class MsSQLQuery:QueryBase
    {
        public MsSQLQuery()
        {
            ParamStyle = "@";
        }
        #region 输出SQL
        /// <summary>
        /// 输出SQL
        /// </summary>
        /// <returns></returns>
        public override string ExportSql()
        {
            if (!string.IsNullOrEmpty(Sql))
            {
                return ExportDeal(Sql);
            }
            StringBuilder sql = new StringBuilder();
            sql.AppendFormat("select {0} from {1}", Columns, Table);
            if (!string.IsNullOrEmpty(Where))
            {
                sql.AppendFormat(" where {0}", Where);
            }
            return ExportDeal(sql.ToString());
        }

        public override string ExportModel()
        {
            if (!string.IsNullOrEmpty(Sql))
            {
                return ExportDeal(Sql);
            }
            StringBuilder sql = new StringBuilder();
            sql.AppendFormat("select top 1 {0} from {1}",Columns,Table);
            if (!string.IsNullOrEmpty(Where))
            {
                sql.AppendFormat(" where {0}", Where);
            }
            return ExportDeal(sql.ToString());
        }

        public override string ExportList()
        {
            if (!string.IsNullOrEmpty(Sql))
            {
                return ExportDeal(Sql);
            }
            StringBuilder strSql = new StringBuilder();
            string strOrder; // -- 排序类型

            strOrder = string.Format("order by {0}", OrderBy);

            //if (string.IsNullOrEmpty(Where))
            //{
            //    strSql.Append(string.Format("select {0} from (select {1}, row_number() over({2}) as row from {3}",
            //                                Columns, Columns, strOrder, Table));
            //    strSql.Append(string.Format(") a where row between {0} and {1}", LimitFirst,
            //                                LimitFirst+LimitLengh-1));
            //}
            //else
            //{
            //    strSql.Append(string.Format("select {0} from (select {1}, row_number() over({2}) as row from {3}",
            //                                Columns, Columns, strOrder, Table));
            //    strSql.Append(string.Format(" where {0}", Where));
            //    strSql.Append(string.Format(") a where row between {0} and {1}", LimitFirst,
            //                                LimitFirst + LimitLengh-1));
            //}
            strSql.Append(string.Format("select * from (select {0}, row_number() over({1}) as row from {2}",
                                            Columns, strOrder, Table));
            if (!string.IsNullOrEmpty(Where))
            {
                strSql.Append(string.Format(" where {0}", Where));
            }
            if (!string.IsNullOrEmpty(GroupBy))
            {
                strSql.Append(string.Format(" group by {0}", GroupBy));
            }
            if (!string.IsNullOrEmpty(Having))
            {
                strSql.Append(string.Format(" having {0}", Having));
            }
            strSql.Append(string.Format(") a where row between {0} and {1}", LimitFirst,
                                        LimitFirst + LimitLengh - 1));
            return ExportDeal(strSql.ToString());
        }

        public override string ExportExists()
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendFormat("select count(1) from {0}", Table);
            if (!string.IsNullOrEmpty(Where))
            {
                sql.AppendFormat(" where {0}", Where);
            }
            return ExportDeal(sql.ToString());
        }

        public override string ExportUpdateFields()
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendFormat("update {0} set {1}", Table,UpdateFields);
            if (!string.IsNullOrEmpty(Where))
            {
                sql.AppendFormat(" where {0}", Where);
            }
            return ExportDeal(sql.ToString());
        }

        public override string ExportDelete()
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendFormat("delete {0}",Table);
            if (!string.IsNullOrEmpty(Where))
            {
                sql.AppendFormat(" where {0}",Where);
            }
            return ExportDeal(sql.ToString());
        }
        #endregion 输出SQL

        #region SQL输出处理
        public override string ExportDeal(string sql)
        {
            return sql.Replace("@", ParamStyle);
        }
        #endregion
    }
}
